import pymssql
import pandas as pd
import pandas.io.sql as sql
from pandas import *
from datetime import date,datetime,timedelta
import matplotlib.pyplot as plt
import math
import enum
import logging
import logging.handlers
from everbright.security import *
from pandas.tseries.offsets import BDay

# Common constant
RiskFreeRate = 0.03
Daily_Risk_Free_Rate =math.pow (1+RiskFreeRate,1/252) - 1
Log_Daily_Risk_Free_Rate  = math.log(Daily_Risk_Free_Rate+1)

def log_setup():
    log_handler = logging.handlers.TimedRotatingFileHandler(filename=r'c:\log\automation.log', when='D', interval=1, backupCount=0, encoding=None, delay=False, utc=False)
    formatter = logging.Formatter('\r\n\r\n--------------%(asctime)s : %(message)s------------------')
    log_handler.setFormatter(formatter)
    logger = logging.getLogger()
    logger.addHandler(log_handler)
    logger.setLevel(logging.INFO)

class YieldPeriod(enum.Enum):
    OneYear=1,
    OneMonth=2,
    OneDay=3,
    SinceBorn=4,
    TwoYear=5,
    ThreeYear=6,
    ThisYear=7

class FundPerformance:
    __index_info_exists=False

    __yield_one_month=math.nan
    __yield_two_month = math.nan
    __yield_three_month = math.nan
    __yield_six_month = math.nan
    __yield_this_year = math.nan
    __yield_two_year = math.nan
    __yield_three_year = math.nan
    __yield_since_born = math.nan
    __annual_yield = math.nan

    "'risk'"
    __anual_vol = math.nan
    __anual_down_risk = math.nan
    __max_draw_down = math.nan
    __max_drawdown_back_days=math.nan

    __beta_to_hs300 = math.nan
    __r2 = math.nan
    __anual_jenson= math.nan
    __anual_sharp_ratio = math.nan
    __anual_sortino = math.nan
    __anual_treynor = math.nan
    __annual_info_error = math.nan

    __win_rate = math.nan
    __positive_rate = math.nan
    __var95_day = math.nan

    def __init__(self,fundId):
        log_setup()
        try:
            self.__fundId = fundId
            self.conn = pymssql.connect(server='.', user='sa', password='qweqwe', database='EverBrightDB')
            query = 'select [FundID],[AsOfDate],[NetValuePerUnit],[NetValue],[AccruedNetValuePerUnit],[MarketValue] ' \
                    ' FROM [EverBrightDB].[dbo].[FundEstimation] WHERE FundID=%s ORDER BY [AsOfDate]' % self.__fundId
            self.daily_value_df = sql.read_sql(query, self.conn, index_col=['AsOfDate'], parse_dates=['AsOfDate'])
            self.daily_value_df = self.daily_value_df[pd.isnull(self.daily_value_df.NetValue)==False]

            self.daily_value_df['log_daily_fund_return'] = np.log( self.daily_value_df['NetValuePerUnit']/self.daily_value_df.shift(1)['NetValuePerUnit'])
            self.daily_value_df['daily_fund_return'] = self.daily_value_df['NetValuePerUnit'] / self.daily_value_df.shift(1)['NetValuePerUnit'] - 1.

            self.current_data = self.daily_value_df.ix[-1]
            self.daily_value_df['NetValuePerUnit_cum_max'] = self.daily_value_df['NetValuePerUnit'].cummax()

            index = IndexSecurity('000300')
            if(hasattr(index,'daily_quote_df')==True):
                self.__index_info_exists=True
                hs300_df = IndexSecurity('000300').daily_quote_df
                self.daily_value_df = pd.merge(self.daily_value_df, hs300_df, left_index=True, right_index=True,
                                               how='left')
                self.daily_value_df['index_daily_close_px'] = hs300_df['ClosePrice']
                self.daily_value_df.rename(columns={'ClosePrice': 'index_daily_close_px'})

                self.daily_value_df['log_daily_index_return'] = np.log(
                    self.daily_value_df['index_daily_close_px'] / self.daily_value_df.shift(1)['index_daily_close_px'])
                self.daily_value_df['daily_index_return'] = self.daily_value_df['index_daily_close_px'] / \
                                                            self.daily_value_df.shift(1)[
                                                                'index_daily_close_px'] - 1.

            # print(self.daily_value_df[:10])
        except Exception as inst:
            print(inst)
            logging.exception("Error happened when initializing fund "+str(self.__fundId))

    def calc_yield(self):
        df = self.daily_value_df
        current_df = self.current_data

        if (len(df.index) > 21):
            prev_df = df.ix[-21]
            self.__yield_one_month = current_df['NetValuePerUnit'] / prev_df['NetValuePerUnit'] - 1.

        if (len(df.index) > 42):
            prev_df = df.ix[-42]
            self.__yield_tow_month = current_df['NetValuePerUnit'] / prev_df['NetValuePerUnit'] - 1.

        if (len(df.index) > 63):
            prev_df = df.ix[-63]
            self.__yield_three_month = current_df['NetValuePerUnit'] / prev_df['NetValuePerUnit'] - 1.

        if (len(df.index) > 126):
            prev_df = df.ix[-126]
            self.__yield_six_month = current_df['NetValuePerUnit'] / prev_df['NetValuePerUnit'] - 1.

        if (len(df.index) > 252):
            prev_df = df.ix[-252]
            self.__yield_this_year = current_df['NetValuePerUnit'] / prev_df['NetValuePerUnit'] - 1.

        if (len(df.index) > 504):
            prev_df = df.ix[-504]
            self.__yield_two_year = current_df['NetValuePerUnit'] / prev_df['NetValuePerUnit'] - 1.

        if (len(df.index) > 756):
            prev_df = df.ix[-756]
            self.__yield_three_year= current_df['NetValuePerUnit'] / prev_df['NetValuePerUnit'] - 1.

        prev_df = df.ix[0]
        self.__yield_since_born = current_df['NetValuePerUnit'] / prev_df['NetValuePerUnit'] - 1.

        numOfPeriods = len(self.daily_value_df.index)
        total_return = current_df['NetValuePerUnit']/df.ix[0,['NetValuePerUnit']]
        self.__annual_yield = math.pow(total_return,252/numOfPeriods)-1

        # print('month %.3f annual %.3f born %.3f' %(self.__yield_one_month,self.__annual_yield,self.__yield_since_born))

    def calc_risk(self):
        "'annual volatility'"
        df = self.daily_value_df['log_daily_fund_return']
        df.fillna(0.)
        self.__anual_vol = df.std()*math.sqrt(252)

        "'down risk'"
        down_log_df =Series(np.where(df>=0.,0.,df))
        self.__anual_down_risk  = down_log_df.std()*math.sqrt(252)

    def calc_max_drawdown(self):
        draw_down_df = self.daily_value_df['NetValuePerUnit']/self.daily_value_df['NetValuePerUnit_cum_max'] - 1.
        self.__max_draw_down = np.min(draw_down_df)
        dff = self.daily_value_df[self.daily_value_df.NetValuePerUnit==self.daily_value_df.NetValuePerUnit_cum_max]
        self.__max_drawdown_back_days = np.busday_count(dff.index[1],dff.index[2])

    def calc_beta(self):
        if(self.__index_info_exists==False):
            return

        fund_df = self.daily_value_df
        # fund_df = pd.merge(fund_df,self.__hs300_df ,left_index=True,right_index=True,how='left')
        log_daily_fund_return= fund_df['log_daily_fund_return']
        log_daily_index_return = fund_df['log_daily_index_return']
        covv =  log_daily_fund_return.cov(log_daily_index_return)
        var_index = log_daily_index_return.var()
        var_fund = log_daily_fund_return.var()
        self.__beta_to_hs300 = np.round(covv/var_index,4)
        self.__r2 = self.__beta_to_hs300 * self.__beta_to_hs300*var_index/var_fund

        index_log_return_mean = log_daily_index_return.mean()
        fund_log_return_mean = log_daily_fund_return.mean()
        jenson = fund_log_return_mean - Log_Daily_Risk_Free_Rate - self.__beta_to_hs300*(index_log_return_mean - Log_Daily_Risk_Free_Rate )
        self.__anual_jenson = math.exp(jenson*252)-1

        self.__anual_sharp_ratio = (self.__annual_yield - RiskFreeRate)/self.__anual_vol
        self.__anual_sortino = (self.__annual_yield - RiskFreeRate)/self.__anual_down_risk
        self.__anual_treynor = (self.__annual_yield - RiskFreeRate)/abs(self.__beta_to_hs300)
        # print(self.__anual_sharp_ratio)
        # print(self.__anual_sortino)
        # print(self.__anual_treynor)

        daily_info_error_arr = log_daily_fund_return - Log_Daily_Risk_Free_Rate - jenson - \
                               self.__beta_to_hs300*(index_log_return_mean - Log_Daily_Risk_Free_Rate)
        daily_std_error = daily_info_error_arr.std()
        self.__annual_info_error = self.__anual_jenson/(daily_std_error*math.sqrt(252))

        self.__win_rate =len(self.daily_value_df[self.daily_value_df['daily_fund_return']>self.daily_value_df['daily_index_return']].index)/len(self.daily_value_df)
        self.__positive_rate = len(self.daily_value_df[self.daily_value_df['daily_fund_return'] > Daily_Risk_Free_Rate ].index) / len(self.daily_value_df)


        # print(self.__positive_rate)

        # print(daily_info_error_arr)
        # print(self.__annual_info_error)
        # print(self.__anual_vol)
        # print(self.__annual_yield)
        # print(self.__anual_sharp_ratio)
        # print(self.daily_value_df.ix[self.daily_value_df.index[0]])

        # daily_return = fund_df['']

    def calc_var(self):
        log_daily_fund_return = self.daily_value_df['log_daily_fund_return']
        normal_log_fund_return = (log_daily_fund_return - log_daily_fund_return.mean())/log_daily_fund_return.std()
        skwReturn = normal_log_fund_return.skew()
        kurtReturn =normal_log_fund_return.kurt()
        meanLogReturnDayF = log_daily_fund_return.mean()
        logReturnFStd = log_daily_fund_return.std()

        q_alpha95 = 1.96
        logVar_cf = (meanLogReturnDayF + logReturnFStd * (q_alpha95+1./6.*(q_alpha95**2-1) * skwReturn + 1./24. *
                          (q_alpha95**3.-3*q_alpha95) * kurtReturn - 1./36. * (2*q_alpha95**3-5*q_alpha95) * skwReturn**2))
        self.__var95_day = - (math.exp(-logVar_cf)-1.)

if __name__ == '__main__':
    data = FundPerformance(2)
    data.calc_yield()
    data.calc_risk()
    # data.calc_beta()
    # data.calc_var()


